package com.common.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.log4j.Logger;

public class DBBaseUtil {

	private static Logger log = Logger.getLogger(DBBaseUtil.class);
	
	/**
	 * @param strSql
	 * @param objects
	 * @return
	 * @throws Exception
	 */
	public static boolean executeUpdate(String strSql,Object...objects) {
		boolean bRet = false;// 返回值
		Connection conn = DBSource.getConnection();// 取得数据联接
		try {
			executeUpdateByConn(conn,strSql,objects);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			log.error(e.getMessage(),e);
		} finally {
			closeConnection(conn,null,null);
		}
		return bRet;// 返回值
	}
	/**
	 * @param strSql
	 * @param objects
	 * @return
	 * @throws Exception
	 */
	public static boolean executeUpdateByConn(Connection conn,String strSql,Object...objects) {
		boolean bRet = false;// 返回值
		PreparedStatement st = null;// 执行语句
		try {
			st = conn.prepareStatement(strSql);// 创建执行语句
			for (int i = 0; i < objects.length; i++) {
				int n = i+1;
				setStatemnt(st, n, objects[i]);
			}
			bRet = st.execute();
		} catch (SQLException sqle) {
			log.error(sqle.getMessage(),sqle);
		} catch (Exception e) {
			log.error(e.getMessage(),e);
		} finally {
			closeConnection(null,st,null);
		}
		return bRet;// 返回值
	}
	
	/**
	 * @param sql
	 * @param objects
	 * @return
	 * @throws Exception
	 */
	public static boolean executeBatchUpdate(String sql,Object...objects)  {
		boolean bRet = false;// 返回值
		Connection conn = null;// 数据库联接
		PreparedStatement st = null;// 执行语句
		try {
			conn = DBSource.getConnection();// 取得数据联接
			st = conn.prepareStatement(sql);// 创建执行语句
			for (int i = 0; i < objects.length; i++) {
				setStatemnt(st, 1, objects[i]);
				st.addBatch();
			}
			st.executeBatch();
			return true;
		} catch (SQLException sqle) {
			log.error("sql 发生异常"+sqle.getMessage(),sqle);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			log.error(e.getMessage(),e);
		} finally {
			closeConnection(conn,st,null);
		}
		return bRet;// 返回值
	}
	
	
	/**
	 * <p>
	 * 关闭Connection连接,ResultSet,Statement
	 * </p>
	 */
	public static void closeConnection(Connection conn, Statement stmt, ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				log.error(e.getMessage(),e);
			}
		}
		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				log.error(e.getMessage(),e);
			}
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				log.error("连接关闭失败。可能连接未创建成功！" + e.getMessage());
			}
		}

	}
	

	
//	**********private********
	
	
	public static Object getObjectSingle(String sql,Object...objects) {
		Connection conn = null;
		Object strRet = 0;
		try {
			conn = DBSource.getConnection();
			return getObjectSingleByConn(conn,sql,objects);
		} catch (Exception e) {
			log.error(e.getMessage(),e);
		} finally {
			closeConnection(conn, null, null);
		}
		return strRet;
	}
	public static Object getObjectSingleByConn(Connection conn,String sql,Object...objects) {

		PreparedStatement stmt = null;
		ResultSet rs = null;
		Object strRet = 0;
		try {
			stmt = conn.prepareStatement(sql, java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE, java.sql.ResultSet.CONCUR_READ_ONLY);
			for(int i=0;i<objects.length;i++){
				int n=i+1;
				setStatemnt(stmt, n, objects[i]);
			}
			rs = stmt.executeQuery();
			if (rs.next())
			strRet = rs.getObject(1);
		} catch (SQLException sqle) {
			log.error(sqle.getMessage(),sqle);
		} catch (Exception e) {
			log.error(e.getMessage(),e);
		} finally {
			closeConnection(null, stmt, rs);
		}
		return strRet;
	}
	protected static void setStatemnt(PreparedStatement stmt,int n,Object o) throws SQLException{
		stmt.setObject(n, o);
	}
}
